/**
 * 
 */
package org.vsg.stock.basic.persistence.hibernate;

import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.vsg.common.hibernate.AbstractGenericDaoImpl;
import org.vsg.stock.basic.domain.GroupingStockInfo;
import org.vsg.stock.basic.domain.RealStockInfo;
import org.vsg.stock.basic.domain.StockInfo;
import org.vsg.stock.basic.persistence.StockInfoDao;

/**
 * @author vison
 *
 */
public class StockInfoDaoImpl extends AbstractGenericDaoImpl implements StockInfoDao {

	/* (non-Javadoc)
	 * @see org.vsg.stock.basic.persistence.StockDailyLineDao#saveAll(java.util.Collection)
	 */
	@Override
	public void saveAll(Collection<StockInfo> entities)
			throws DataAccessException {
		// TODO Auto-generated method stub
		this.getTemplate().saveOrUpdateAll(entities);
		logger.debug("update and save entity " );
	}

	@Override
	public List<StockInfo> findAllInfo() throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity ");
		query.append("FROM ").append(StockInfo.class.getName());
		query.append(" as entity ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<StockInfo> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		StockInfo entity = null;
		List<StockInfo> entiyies = new Vector<StockInfo>();
		
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		
		return entiyies;
	}

	@Override
	public List<String> findAllName() throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity.name ");
		query.append("FROM ").append(StockInfo.class.getName());
		query.append(" as entity ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<String> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		List<String> entiyies = new Vector<String>();
		
		String entity = null;
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		return entiyies;
	}

	
	
	@Override
	public List<String> findAllCode() throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity.code ");
		query.append("FROM ").append(StockInfo.class.getName());
		query.append(" as entity ");
		query.append(" ORDER BY entity.code ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<String> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		List<String> entiyies = new Vector<String>();
		
		String entity = null;
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		return entiyies;
	}

	
	@Override
	public List<String> fineStockCodeByCategoryDescription(String categoryName)
			throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity.code ");
		query.append("FROM ").append(RealStockInfo.class.getName()).append(" as entity ");
		query.append("JOIN entity.assoStockCategory as category ");
		query.append("WHERE category.description = ? ");
		
		Collection params = new Vector();
		params.add( categoryName );
		
		logger.debug("query : " + query.toString());
		
		Iterator<String> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		List<String> entiyies = new Vector<String>();
		
		String entity = null;
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		return entiyies;
	}

	@Override
	public void cleanAll() throws DataAccessException {
		// TODO Auto-generated method stub
		HibernateCallback action = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query query = sess.createSQLQuery("truncate table STOCK_INFO");
				int result = query.executeUpdate();
				return new Integer(result);
			}
			
		};
		
		getTemplate().execute(action);
		
		logger.info("clean up StockInfo data");
	}

	@Override
	public boolean checkIdExist(String stockCode) throws DataAccessException {
		// TODO Auto-generated method stub
		boolean result = false;
		
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("count(*) ");
		query.append("FROM ").append(StockInfo.class.getName()).append(" as entity ");
		query.append("WHERE entity.code = ? ");
		
		Collection params = new Vector();
		params.add( stockCode );
		
		logger.debug("query : " + query.toString());
		
		List list  = getTemplate().find(query.toString() , params.toArray());
		if (Integer.parseInt(list.get(0).toString()) >0 ) {
			result = true;
		}
		return result;
	}

	
	
	@Override
	public List<String> findAllGroupingCode() throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity.code ");
		query.append("FROM ").append(GroupingStockInfo.class.getName());
		query.append(" as entity ");
		query.append(" ORDER BY entity.code ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<String> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		List<String> entiyies = new Vector<String>();
		
		String entity = null;
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		return entiyies;
	}
	

	@Override
	public List<GroupingStockInfo> findAllGroupingInfo()
			throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity ");
		query.append("FROM ").append(GroupingStockInfo.class.getName());
		query.append(" as entity ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<GroupingStockInfo> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		GroupingStockInfo entity = null;
		List<GroupingStockInfo> entiyies = new Vector<GroupingStockInfo>();
		
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		
		return entiyies;
	}

	@Override
	public List<RealStockInfo> findAllRealInfo() throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity ");
		query.append("FROM ").append(RealStockInfo.class.getName());
		query.append(" as entity ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<RealStockInfo> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		RealStockInfo entity = null;
		List<RealStockInfo> entiyies = new Vector<RealStockInfo>();
		
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		
		return entiyies;
	}

	@Override
	public void cleanInfoPartMap() throws DataAccessException {
		// TODO Auto-generated method stub
		HibernateCallback action = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query query = sess.createSQLQuery("truncate table STOCK_INFO_PART_MAP");
				int result = query.executeUpdate();
				return new Integer(result);
			}
			
		};
		
		getTemplate().execute(action);
		
		logger.info("clean up Stock Info Part Map data");		
	}

	@Override
	public void updateStockInfoPartMap(final String groupingStkCode,
			final List<String> realStkCodeList) throws DataAccessException {
		// TODO Auto-generated method stub
		
		final StringBuilder queryStr = new StringBuilder("INSERT INTO ");
		queryStr.append("stock_info_part_map ");
		queryStr.append("(g_stock_code , r_stock_code) ");
		queryStr.append("SELECT  ");
		queryStr.append("? , ? ");
		queryStr.append("FROM stock_info_part_map AS sipm ");
		queryStr.append("WHERE sipm.g_stock_code = ? AND sipm.r_stock_code = ? ");
		queryStr.append("HAVING count(*) = 0 ");

		HibernateCallback callBack = new HibernateCallback() {
			
			public Object doInHibernate(Session session) {

				logger.debug( queryStr.toString() );
				
				String code = null;

				
				int count = 0;

				for (Iterator<String> entitiesIter = realStkCodeList.iterator() ; entitiesIter.hasNext(); ) {
					code = entitiesIter.next();
					
					Query query = session.createSQLQuery(queryStr.toString());
					query.setString(0, groupingStkCode);
					query.setString(1, code);
					query.setString(2, groupingStkCode);
					query.setString(3, code);
					
					count+=query.executeUpdate();
					
					entitiesIter.remove();

				}
				return new Integer(count);
			}
		};
		
		this.getTemplate().execute(callBack);
		
		
	}

	@Override
	public int totalACapital(String market) throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("sum(entity.aCapital) ");
		query.append("FROM ").append(RealStockInfo.class.getName()).append(" as entity ");
		query.append("WHERE entity.code like ? ");
		
		Collection params = new Vector();
		params.add( market+"%" );
		
		logger.debug("query : " + query.toString());
		
		List list  = getTemplate().find(query.toString() , params.toArray());
		
		int total = Integer.parseInt(list.get(0).toString());
		
		return total;
	}

	@Override
	public int totalGroupingACatital(String groupStockInfoCode)
			throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("sum(realPart.aCapital) ");
		query.append("FROM ").append(GroupingStockInfo.class.getName()).append(" as groupEnt ");
		query.append("LEFT JOIN GroupingStockInfo.assoPart ").append(" as realPart ");		
		query.append("WHERE groupingStockInfo.code = ? ");
		
		Collection params = new Vector();
		params.add( groupStockInfoCode );
		
		logger.debug("query : " + query.toString());
		
		List list  = getTemplate().find(query.toString() , params.toArray());
		
		int total = Integer.parseInt(list.get(0).toString());
		
		return total;
	}

	

}
